﻿using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

internal class Database
{
    SqlConnection connection;
    string connectionString = "Data Source=192.168.5.97;Initial Catalog=GL_ElectricMeter;User ID=sa;Password=gl.2008;Encrypt=false;TrustServerCertificate=true;";

    public Database()
    {
        try
        {
            connection = new SqlConnection(connectionString);
            connection.Open();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return;
        }
    }

    public async Task InsertDatabase(object obj, string tableName)
    {
        Type type = obj.GetType();
        PropertyInfo[] properties = type.GetProperties();
        List<string> columnNames = new List<string>();
        List<string> parameterNames = new List<string>();
        List<string> value = new List<string>();
        foreach (PropertyInfo property in properties)
        {
            if (property.Name == "id") continue;
            string columnName = property.Name;
            string parameterName = $"@{columnName}";
            columnNames.Add(columnName);
            parameterNames.Add(parameterName);
            //value.Add(property.GetValue(obj).ToString());
            object propertyValue = property.GetValue(obj);
            if (propertyValue != null)
            {
                value.Add(propertyValue.ToString());
            }
            else
            {
                value.Add("");
            }
        }
        string columns = string.Join(", ", columnNames);
        string parameters = string.Join(", ", parameterNames);
        string insertQuery = $"INSERT INTO {tableName} ({columns}) VALUES ({parameters})";
        using (SqlConnection sqlConnection = new SqlConnection(connectionString))
        {
            await sqlConnection.OpenAsync();
            using (SqlCommand sqlcd = new SqlCommand(insertQuery, sqlConnection))
            {
                for (int i = 0; i < parameterNames.Count; i++)
                {
                    sqlcd.Parameters.AddWithValue(parameterNames[i], value[i]);
                }
                sqlcd.ExecuteNonQuery();
            }
        }

    }

    public async Task<bool> SelectData(string query)
    {
        using (SqlConnection sqlConnect = new SqlConnection(connectionString))
        {
            await sqlConnect.OpenAsync();
            using (SqlCommand command = new SqlCommand(query, sqlConnect))
            {
                using (SqlDataReader sr = await command.ExecuteReaderAsync())
                {
                    if (sr.HasRows)
                    {
                        while (sr.Read())
                        {
                            return true;
                        }
                    }
                }
            }
        }

        return false;
    }

    public async Task UpdateDate(object obj, string query, string table)
    {
        try
        {
            Type type = obj.GetType();
            PropertyInfo[] properties = type.GetProperties();
            List<string> columnNames = new List<string>();
            List<string> value = new List<string>();
            foreach (PropertyInfo property in properties)
            {
                string columnName = property.Name;
                string str_update = "";
                using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                {
                    await sqlConnection.OpenAsync();
                    using (SqlCommand command = new SqlCommand(query, sqlConnection))
                    {
                        using (SqlDataReader sr = await command.ExecuteReaderAsync())
                        {
                            if (sr.HasRows)
                            {
                                while (sr.Read())
                                {
                                    str_update = $"update {table} set {columnName}='{property.GetValue(obj)}' where id='{sr["id"]}'";
                                }
                            }
                        }
                    }
                }
                if (str_update != "")
                {
                    using (SqlConnection sqlConnect = new SqlConnection(connectionString))
                    {
                        await sqlConnect.OpenAsync();
                        using (SqlCommand sql = new SqlCommand(str_update, sqlConnect))
                        {
                            await sql.ExecuteNonQueryAsync();
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            return;
        }



    }
}
